/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package dao;

/**
 *
 * @author Hugo
 */
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import javax.swing.JOptionPane;
import model.Aluno;
import java.sql.Statement;
import model.Endereco;

/**
 *
 * @author Hugo
 */
public class AlunoDAO {

    private Connection connection;

    public AlunoDAO() {
        this.connection = new ConnectionFactory().getConnection();
    }

    /**
     *
     * @param aluno
     * @return
     */
    public int incluir(Aluno aluno) {
        String sql = "insert into aluno (cpf, nome, idade, id_endereco) values (?,?,?,?)";
        int id = -1;
        try {
            EnderecoDAO daoEndereco = new EnderecoDAO();
            int idNovoEndereco = daoEndereco.incluir(aluno.getEndereco());
            if (idNovoEndereco != -1) {
                PreparedStatement stmt = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);

                stmt.setString(1, aluno.getCpf());
                stmt.setString(2, aluno.getNome());
                stmt.setInt(3, aluno.getIdade());
                stmt.setInt(4, idNovoEndereco);
                stmt.execute();
                ResultSet rs = stmt.getGeneratedKeys();
                if (rs.next()) {
                    id = rs.getInt(1);
                }
                stmt.close();
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return id;
    }

    public int alterar(Aluno aluno) {
        String sql = "update aluno set cpf = ?, nome = ?, idade = ?, id_endereco = ? where id = ?";
        int id = -1;
        try {
            PreparedStatement stmt = connection.prepareStatement(sql);
            stmt.setString(1, aluno.getCpf());
            stmt.setString(2, aluno.getNome());
            stmt.setInt(3, aluno.getIdade());
            stmt.setInt(4, aluno.getEndereco().getId());
            stmt.setInt(5, aluno.getMatricula());
            stmt.execute();            
            EnderecoDAO enderecoDao = new EnderecoDAO();
            enderecoDao.alterar(aluno.getEndereco());
            id = aluno.getMatricula();
            stmt.close();            
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return id;
    }

    public Aluno excluir(int matricula) {
        Aluno aluno = null;
        try {
            aluno = buscar(matricula);
            if (aluno != null) {
                String sql = "delete from aluno where id = ?";
                PreparedStatement stmt1 = connection.prepareStatement(sql);
                stmt1.setInt(1, matricula);
                stmt1.execute();
                stmt1.close();

                EnderecoDAO enderecoDao = new EnderecoDAO();
                enderecoDao.excluir(aluno.getEndereco());
            }

        } catch (SQLException e) {
            JOptionPane.showMessageDialog(null, "Não é possível excluir");
        }
        return aluno;
    }

    public Aluno buscar(int matricula) {
        String sql = "select * from aluno inner join endereco on aluno.id_endereco = endereco.id where aluno.id = ?";
        Aluno aluno = null;
        try {
            PreparedStatement stmt = connection.prepareStatement(sql);
            stmt.setString(1, Integer.toString(matricula));
            stmt.execute();
            ResultSet rs = stmt.getResultSet();

            while (rs.next()) {
                Endereco e = new Endereco();
                e.setId(rs.getInt("endereco.id"));
                e.setCep(rs.getString("cep"));
                e.setCidade(rs.getString("cidade"));
                e.setEstado(rs.getString("estado"));
                e.setLogradouro(rs.getString("logradouro"));
                e.setNumero(rs.getInt("numero"));
                e.setComplemento(rs.getString("complemento"));
                e.setBairro(rs.getString("bairro"));
                
                aluno = new Aluno();
                aluno.setMatricula(rs.getInt("aluno.id"));
                aluno.setIdade(rs.getInt("idade"));
                aluno.setCpf(rs.getString("cpf"));
                aluno.setNome(rs.getString("nome"));
                aluno.setEndereco(e);
            }
            stmt.close();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return aluno;
    }

    public List<Aluno> listar() {
        List<Aluno> alunos = new ArrayList<>();
        String sql = "select * from aluno inner join endereco on aluno.id_endereco = endereco.id";
        try {
            PreparedStatement stmt = connection.prepareStatement(sql);
            stmt.execute();
            ResultSet rs = stmt.getResultSet();

            while (rs.next()) {
                Endereco e = new Endereco();
                e.setId(rs.getInt("endereco.id"));
                e.setCep(rs.getString("cep"));
                e.setCidade(rs.getString("cidade"));
                e.setEstado(rs.getString("estado"));
                e.setLogradouro(rs.getString("logradouro"));
                e.setNumero(rs.getInt("numero"));
                e.setComplemento(rs.getString("complemento"));
                e.setBairro(rs.getString("bairro"));

                Aluno a = new Aluno();
                a.setMatricula(rs.getInt("aluno.id"));
                a.setIdade(rs.getInt("idade"));
                a.setCpf(rs.getString("cpf"));
                a.setNome(rs.getString("nome"));
                a.setEndereco(e);
                alunos.add(a);
            }

            stmt.close();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }

        return alunos;
    }
}
